#include "librarydatabase.h"
/*
 * 使用SQlite数据库，没有连接服务器
 * SQlite使用三个类QSqlDatabase、QSqlQuery、QSqlQueryModel
 * QSqlDatabase：创建数据库
 * QSqlQuery：使用sql语句创建表头等等
 * QSqlQueryModel：读取数据库，变为一个model,model在TableView上显示
 */
libraryDataBase::libraryDataBase()
{
   //创建数据库并创建表格
   db1 = QSqlDatabase::addDatabase("QSQLITE");
   db1.setDatabaseName("library.db");
   if(!db1.open())
   {
       qDebug("open is false");
   }
   else
   {
       qDebug("open is OK");
   }
   //创建一个表格，主键值为id,20字符的名字、20字符的学号
   QSqlQuery query(db1);//使query关联数据库
   query.exec("create table student (id int primary key, name QString,image QString,major QString,grade QString,booklist BLOB)");

   //创建model
   model = new QSqlQueryModel;
   model->setQuery("select * from student");
   model->setHeaderData(0, Qt::Horizontal, "学号");
   model->setHeaderData(1, Qt::Horizontal, "姓名");
   model->setHeaderData(2, Qt::Horizontal, "选项");
}
void libraryDataBase::addLine(Student* sm)
{
    QSqlQuery query(db1);//使query关联数据库
    //注意ID号不能重复，否则直接覆盖
    QString sql = "insert into student(id,name,image,major,grade,bookList) values(:id,:name,:image,:major,:grade,:bookList)";
    query.prepare(sql);
    query.bindValue(":id", sm->id);
    query.bindValue(":name", sm->name);
    query.bindValue(":image",sm->picturePath);
    query.bindValue(":major",sm->major);
    query.bindValue(":grade",sm->grade);
//    //存图片-----------------start--------------
//    QPixmap* inPixmap = new QPixmap(sm->picturePath);
//    QByteArray inByteArray;
//    QBuffer inBuffer(&inByteArray);
//    inBuffer.open(QIODevice::WriteOnly);
//    inPixmap->save(&inBuffer, "PNG");//以PNG格式存储
//    query.bindValue(":image", inByteArray);//将图片数组放入数据库
//    //存图片-------------------end----------------
    //存借书的列表
    QByteArray data;
    foreach (QString c, sm->borrowBookList) {
        data.append(c.toUtf8());
        data.append(QString("\r\n").toUtf8());
    }
    query.bindValue(":bookList",data);//存放还没还的书的列表
    query.exec();
    model->setQuery("select id,name,image,major,grade from student");
}
void libraryDataBase::deleteLine(int id)
{
    QSqlQuery query(db1);//使query关联数据库
    query.exec(QString("DELETE FROM student WHERE id = %1").arg(id));
    model->setQuery("select id,name,image,major,grade from student");
}
void libraryDataBase::changeLine(Student* sm)
{
    //改变书的列表
    QSqlQuery query(db1);//使query关联数据库
    QByteArray data;
    foreach (QString c, sm->borrowBookList) {
        data.append(c.toUtf8());
        data.append(QString("\r\n").toUtf8());
    }
    query.prepare("UPDATE  student  SET bookList =:bookList WHERE id =:id ");
    query.bindValue(":bookList",data);
    query.bindValue(":id",sm->id);
    query.exec();
    model->setQuery("select id,name,image,major,grade from student");
}
QString libraryDataBase::getBookName(QString t)
{
    for(int i=0;i<bookdatabase->bookmodel->columnCount();i++)
    {
        if(bookdatabase->bookmodel->data(bookdatabase->bookmodel->index(i,0)).toString()==t)
        {
            return bookdatabase->bookmodel->data(bookdatabase->bookmodel->index(i,1)).toString();
        }
    }
    return (QString("NULL"));
}

Student* libraryDataBase::getLine(QString id)
{
    Student* st=new Student();
    QSqlQuery query(db1);//使query关联数据库
    query.exec(QString("SELECT * FROM student WHERE id = '%1'").arg(id));
    query.next();//必须执行才能查看到数据
    QString getid = query.value(0).toString();
    QString getname = query.value(1).toString();
    QString getpicturePath = query.value(2).toString();
    QString getmajor = query.value(3).toString();
    QString getgrade = query.value(4).toString();
    QString getbooklist = query.value(5).toByteArray();
    st->id=getid;
    st->name=getname;
    st->picturePath=getpicturePath;
    st->major=getmajor;
    st->grade=getgrade;
    int i=0;
    st->borrowBookList.clear();
    while(1)
    {
        QString temp=getbooklist.section("\r\n",i,i);
        if(temp!="")
        {
            qDebug()<<temp;
            //根据编号转化为中文书名
            temp=this->getBookName(temp);
            st->borrowBookList.append(temp);
        }
        else
        {
            break;
        }
        i++;
    }
    return st;
}
